package com.zr.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.Getscoredaohh;
import com.zr.model.Grade;

import com.zr.util.JDBCUtil;

public class GetscoredaoImplhh implements Getscoredaohh {

	@Override
	public int getcountstudnets(String cname) {
		int scount=0;
		StringBuffer  sql= new StringBuffer("SELECT COUNT(student.sid) FROM course"
				+ " INNER JOIN s_c"
				+ " on course.cid=s_c.cid"
				+" INNER JOIN student on student.sid=s_c.sid"
				+" WHERE course.cname=? ");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setString(1, cname);
		
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				scount=rs.getInt("COUNT(student.sid)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	@Override
	public List<Grade> getscore(String cname, int page, int rows) {
		List<Grade> scores=new ArrayList<Grade>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT student.sid,student.sname,s_c.score FROM course");
		sql.append(" INNER JOIN s_c on course.cid=s_c.cid");
		sql.append(" INNER JOIN student on student.sid=s_c.sid");
		sql.append(" WHERE course.cname=?");
		sql.append(" ORDER BY (s_c.score+0) DESC");
		sql.append(" limit ?,?");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setString(1, cname);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet  rs = pst.executeQuery();
			while (rs.next()) {
				Grade s = new Grade();	
				s.setSid(rs.getInt("sid"));
				s.setSname(rs.getString("sname"));
				s.setScore(rs.getString("score"));
				
				scores.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scores;
	}

	@Override
	public boolean updescore(int sid,String tname,String score,String cname,String nr,String zt,String sj) {
		boolean flag=false;
		StringBuffer sql= new StringBuffer(" insert into applyscore(sid,tname,cname,nr,zt,sj,score) VALUES(?,?,?,?,?,?,?)");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setString(2, tname);
			pst.setString(3, cname);
			pst.setString(4, nr);
			pst.setString(5, zt);
			pst.setString(6, sj);
			pst.setString(7, score);
			int i=pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}

	@Override
	public boolean addscore(int sid, String cname, String score) {
		boolean flag=false;
		StringBuffer sql= new StringBuffer("");
		sql.append(" UPDATE s_c INNER JOIN course");
		sql.append(" ON s_c.cid=course.cid");
		sql.append(" SET s_c.score=? ");
		sql.append(" WHERE course.cname=? AND s_c.sid=? and (s_c.score IS NULL or s_c.score='')");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(3, sid);
			pst.setString(2, cname);
			pst.setString(1, score);
		
			int i=pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
	@Override	
	public String avgcore(String cname) {
		DecimalFormat df = new DecimalFormat("0.00");
		String avg=null;
		StringBuffer  sql= new StringBuffer("");
		sql.append(" SELECT avg(0+s_c.score ) as a FROM course");
		sql.append(" INNER JOIN s_c");
		sql.append(" on course.cid=s_c.cid");
		sql.append(" WHERE course.cname=? AND s_c.sid<>'' and s_c.score <>''");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setString(1, cname);
		
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){

				float avgs=(float)rs.getFloat("a");
				avg=df.format(avgs);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return avg;
	}
@Override
public String passcore(String cname) {
	DecimalFormat df = new DecimalFormat("0.00");
	GetscoredaoImplhh cou= new GetscoredaoImplhh();
	int counts=cou.getcjylrcountstudnets(cname);
	String passcore=null;
	StringBuffer  sql= new StringBuffer("");
	sql.append(" SELECT   COUNT(s_c.score) as a FROM course");
	sql.append(" INNER JOIN s_c");
	sql.append(" on course.cid=s_c.cid");
	sql.append(" WHERE course.cname=? AND s_c.score>=60 AND s_c.sid<>''");
	Connection  con = JDBCUtil.getConnection();
	try {
		PreparedStatement  pst = con.prepareStatement(sql.toString());
		pst.setString(1, cname);
	
		ResultSet  rs = pst.executeQuery();
		if(rs.next()){
			float num= (float)rs.getInt("a")/counts;
			String num1 = df.format(num*100);
			passcore=num1+"%";
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return passcore;
}
@Override
public String getygscorebyid(int sid, String sname,String cname) {
	String  xsscore=null;
	StringBuffer  sql= new StringBuffer("");
	sql.append(" SELECT   s_c.score  FROM student");
	sql.append(" INNER JOIN s_c");
	sql.append(" on student.sid=s_c.sid");
	sql.append(" INNER JOIN course");
	sql.append(" ON s_c.cid=course.cid");
	sql.append(" WHERE (student.sid=? or student.sname=?) AND course.cname=?");
	Connection  con = JDBCUtil.getConnection();
	try {
		PreparedStatement  pst = con.prepareStatement(sql.toString());
		pst.setInt(1, sid);
	    pst.setString(2, sname);
	    pst.setString(3, cname);
		ResultSet  rs = pst.executeQuery();
		if(rs.next()){
			xsscore=rs.getString("s_c.score");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return xsscore;
}

	//根据tid查找教师姓名
@Override
public String getTnameByTid(int tid) {
	String  tname=null;
	StringBuffer  sql= new StringBuffer("");
	sql.append(" SELECT  tname  FROM teacher");
	sql.append(" where tid = ?");
	Connection  con = JDBCUtil.getConnection();
	try {
		PreparedStatement  pst = con.prepareStatement(sql.toString());
		pst.setInt(1, tid);
		ResultSet  rs = pst.executeQuery();
		if(rs.next()){
			tname = rs.getString("tname");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return tname;
}
public int getcjcountstudnets(String cname) {
	int scount=0;
	StringBuffer  sql= new StringBuffer("SELECT COUNT(student.sid) FROM course"
			+ " INNER JOIN s_c"
			+ " on course.cid=s_c.cid"
			+" INNER JOIN student on student.sid=s_c.sid"
			+" WHERE course.cname=? ");
	Connection  con = JDBCUtil.getConnection();
	try {
		PreparedStatement  pst = con.prepareStatement(sql.toString());
		pst.setString(1, cname);
	
		ResultSet  rs = pst.executeQuery();
		if(rs.next()){
			scount=rs.getInt("COUNT(student.sid)");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return scount;
}
public int getcjylrcountstudnets(String cname) {
	int scount=0;
	StringBuffer  sql= new StringBuffer("SELECT COUNT(student.sid) FROM course"
			+ " INNER JOIN s_c"
			+ " on course.cid=s_c.cid"
			+" INNER JOIN student on student.sid=s_c.sid"
			+" WHERE course.cname=? and s_c.score<>''and s_c.score is not null");
	Connection  con = JDBCUtil.getConnection();
	try {
		PreparedStatement  pst = con.prepareStatement(sql.toString());
		pst.setString(1, cname);
	
		ResultSet  rs = pst.executeQuery();
		if(rs.next()){
			scount=rs.getInt("COUNT(student.sid)");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return scount;
}
}
